# -*- coding: utf-8 -*-
'''
@author Rafael Passos & Ruben Ribeiro
@date: 3 de Dezembro de 2012
@obs: Módulo referente à leitura do ficheiro em formato .xls
e criação/escrita da base de dados sqlite3.
'''

import sqlite3
import xlrd

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,  create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

base = declarative_base()
db_session = scoped_session(sessionmaker())

class DadosTabela(base):
	'''
	Cria a variável __tablename__ com uma tabela dadosinscritos
	e com as definições das colunas que vão ser depois preenchidas.
	'''
	__tablename__ = "dadosInscritos"
	id = Column(Integer, primary_key=True)
	EstabelecimentoEnsino = Column(String(50))
	UnidadeOrganica = Column(String(50))
	NivelFormacao = Column(String(50))
	Cursos = Column(String(50))
	a1995_96 = Column(String(10))
	a1996_97 = Column(String(10))
	a1997_98 = Column(String(10))
	a1998_99 = Column(String(10))
	a1999_00 = Column(String(10))
	a2000_01 = Column(String(10))
	a2001_02 = Column(String(10))
	a2002_03 = Column(String(10))
	a2003_04 = Column(String(10))
	a2004_05 = Column(String(10))
	a2005_06 = Column(String(10))
	a2006_07 = Column(String(10))
	a2007_08 = Column(String(10))
	a2008_09 = Column(String(10))
	a2009_10 = Column(String(10))
	a2010_11 = Column(String(10))

def init_sqlalchemy():
	'''
	Esta função cria um ficheiro de base de dados, abre um ficheiro
	excel, preenche a tabela já criada com os valores pretendidos 
	deste ficheiro excel e insere esta mesma tabela na base de dados.
	'''
	try:
		ficheiro_excel = xlrd.open_workbook("Inscritos_2010-2011.xls")
		global basedados
		directoria = 'sqlite:///sqlalchemy.db'
		basedados = create_engine(directoria, echo=True)
		db_session.configure(bind=basedados, autoflush=False, expire_on_commit=False)
		base.metadata.drop_all(basedados) #Apaga a tabela se esta já existir
		base.metadata.create_all(basedados)
	except:
		print "Não existe ficheiro .xls"
		
	est_ensino = ''
	unid_org = ''
	nivel_form = ''
	folha_excel = ficheiro_excel.sheet_by_name("Tabela 4.1")
	
	primeira_linha = 4
	ultima_linha = folha_excel.nrows - 9
	est_ensino_num_col = 0
	unid_org_num_col = 1
	nivel_form_num_col = 2
	cursos_num_col = 3
	a95_96_num_col = 7
	a96_97_num_col = 10
	a97_98_num_col = 13
	a98_99_num_col = 16
	a99_00_num_col = 19
	a00_01_num_col = 22
	a01_02_num_col = 25
	a02_03_num_col = 28
	a03_04_num_col = 31
	a04_05_num_col = 34
	a05_06_num_col = 37
	a06_07_num_col = 40
	a07_08_num_col = 43
	a08_09_num_col = 47
	a09_10_num_col = 50
	a10_11_num_col = 53
	
	basedados.execute(
	DadosTabela.__table__.insert(),
	[{"EstabelecimentoEnsino": EstEnsino(n, folha_excel, est_ensino_num_col), 
	"UnidadeOrganica": UniOrganica(n, folha_excel, unid_org_num_col), 
	"NivelFormacao": NivelFormacao(n, folha_excel, nivel_form_num_col), 
	"Cursos": folha_excel.cell(n, cursos_num_col).value, 
	"a1995_96": folha_excel.cell(n, a95_96_num_col).value, 
	"a1996_97": folha_excel.cell(n, a96_97_num_col).value, 
	"a1997_98": folha_excel.cell(n, a97_98_num_col).value, 
	"a1998_99": folha_excel.cell(n, a98_99_num_col).value, 
	"a1999_00": folha_excel.cell(n, a99_00_num_col).value, 
	"a2000_01": folha_excel.cell(n, a00_01_num_col).value,
	"a2001_02": folha_excel.cell(n, a01_02_num_col).value,
	"a2002_03": folha_excel.cell(n, a02_03_num_col).value,
	"a2003_04": folha_excel.cell(n, a03_04_num_col).value,
	"a2004_05": folha_excel.cell(n, a04_05_num_col).value,
	"a2005_06": folha_excel.cell(n, a05_06_num_col).value,
	"a2006_07": folha_excel.cell(n, a06_07_num_col).value,
	"a2007_08": folha_excel.cell(n, a07_08_num_col).value,
	"a2008_09": folha_excel.cell(n, a08_09_num_col).value,
	"a2009_10": folha_excel.cell(n, a09_10_num_col).value,
	"a2010_11": folha_excel.cell(n, a10_11_num_col).value} for n in range(primeira_linha, ultima_linha)]
	)

def EstEnsino(n, folha_excel, est_ensino_num_col): 
	'''
	Na coluna Estabelecimento de Ensino, verifica se a célula está 
	vazia, se sim, retorna o valor da última célula não vazia 
	nesta coluna.	
	@param n - número da linha da tabela do ficheiro .xls
	@param folhaEXCEL - folha que abrimos do ficheiro .xls
	@param est_ensino_num_col - número da coluna que contém os estabelecimentos de ensino
	@return est_ensino - string que contem o nome de um estabelecimento de ensino
	'''
	if (not (folha_excel.cell(n, est_ensino_num_col).value == '')):
		init_sqlalchemy.est_ensino = folha_excel.cell(n, est_ensino_num_col).value
		return init_sqlalchemy.est_ensino
	else:
		init_sqlalchemy.estE = init_sqlalchemy.est_ensino 
		return init_sqlalchemy.est_ensino 
                    
def UniOrganica(n, folha_excel, unid_org_num_col): 
	'''
	Na coluna Unidade Orgânica, verifica se a célula está 
	vazia, se sim, retorna o valor da última célula não vazia 
	nesta coluna.
	@param n - número da linha da tabela do ficheiro .xls
	@param folhaEXCEL - folha que abrimos do ficheiro .xls
	@param unid_org_num_col - número da coluna que contém as unidades orgânicas
	@return unid_org - string que contem o nome de uma unidade orgânica
	'''
	if (not (folha_excel.cell(n, unid_org_num_col).value == '')):
		init_sqlalchemy.unid_org = folha_excel.cell(n, unid_org_num_col).value
		return init_sqlalchemy.unid_org
	else:
		init_sqlalchemy.unid_org = init_sqlalchemy.unid_org 
		return init_sqlalchemy.unid_org   
        
def NivelFormacao(n, folha_excel, nivel_form_num_col):
	'''
	Na coluna Nível de Formação, verifica se a célula está 
	vazia, se sim, retorna o valor da última célula não vazia 
	nesta coluna.
	@param n - número da linha da tabela do ficheiro .xls
	@param folhaEXCEL - folha que abrimos do ficheiro .xls
	@param nivel_form_num_col - número da coluna que contém os níveis de formação
	@return nivel_form - string que contem o nome de um nível de formação
	'''
	if (not (folha_excel.cell(n, nivel_form_num_col).value == '')):
		init_sqlalchemy.nivel_form = folha_excel.cell(n, nivel_form_num_col).value
		return init_sqlalchemy.nivel_form
	else:
		init_sqlalchemy.nivel_form = init_sqlalchemy.nivel_form
		return init_sqlalchemy.nivel_form
